package t_pack;

import javafx.scene.chart.CategoryAxis;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.AxisLocation;
import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.labels.StandardCategoryToolTipGenerator;
import org.jfree.chart.labels.StandardPieToolTipGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.xy.XYSeries;
import org.jfree.data.xy.XYSeriesCollection;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ItemEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.util.List;

public class Main extends JFrame {

    class StringBean {
        private String id;
        private String name;

        StringBean(String id, String name) {
            this.id = id;
            this.name = name;
        }

        public String toString() {
            return this.name;
        }

        public String getId() {
            return this.id;
        }
    }

    private StringBean currentRegion, currentCompGroup;
    private Statement statement;
    private boolean onlyEnrolled;

    public static void main (String[] arg) throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://10.0.16.212/abitv3?useUnicode=true&characterEncoding=utf8","abitv3","==6HaQ#8md");
        Main main = new Main();
        main.statement = connection.createStatement();
        main.showGUI();

    }

    private JFreeChart getPieChart() throws Exception {
        String query = "SELECT sum(is_noexam) AS countNoexam, " +
                "sum(outofcompetition) AS countOutofcompetition, " +
                "sum(IF(abit_entrant.ref_abit_target_regions<>NULL, 1, 0)) AS countTarget, " +
                "count(*) AS countAll " +
                "FROM abit_entrant JOIN abit_abiturients ON ref_abit_abiturients = abit_abiturients.id " +
                "WHERE ref_abit_campaigns = 1";
        //Отбор только по зачисленным
        if (this.onlyEnrolled) query += " AND ref_abit_eabitstate IN (8,9,10,12,13,14)";

        //Отбор по региону
        if (this.currentRegion.getId() != null) query += " AND ref_abit_region_registrations = " + this.currentRegion.getId();

        //Отбор по конкурсным группам
        if (this.currentCompGroup.getId() != null) query += " AND ref_abit_abiteducompgroup = " + this.currentCompGroup.getId();

        DefaultPieDataset dataset = new DefaultPieDataset();
        ResultSet rs1 = this.statement.executeQuery(query);
        rs1.next();
        int countNoexam = rs1.getInt("countNoexam");
        int countOutofcompetition = rs1.getInt("countOutofcompetition");
        int countTarget = rs1.getInt("countTarget");
        int countAll = rs1.getInt("countAll");
        dataset.setValue("Общий конкурс", countAll - countTarget - countOutofcompetition - countNoexam);
        dataset.setValue("Целевой прием", countTarget);
        dataset.setValue("Без вступительных", countNoexam);
        dataset.setValue("Вне конкурса", countOutofcompetition);
        JFreeChart pieChart = ChartFactory.createPieChart(
                "Распределение по типам конкурса",
                dataset,
                false,
                true,
                false
        );
        PiePlot plot = (PiePlot) pieChart.getPlot();
        plot.setToolTipGenerator(new StandardPieToolTipGenerator());
        return pieChart;
    }

    private JFreeChart getChart() throws Exception{
        ResultSet averageEge = this.averageEge();
        Map<String, Double> mapOfAverageEge = new HashMap<>();
        while (averageEge.next()) mapOfAverageEge.put(averageEge.getString("name"), averageEge.getDouble("average"));

        ArrayList<String> listOfName = new ArrayList<>(mapOfAverageEge.keySet());
        listOfName.sort(Comparator.<String>naturalOrder());
        Iterator<String> iteratorListOfAverageEge = listOfName.iterator();
        String name;
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();

        while (iteratorListOfAverageEge.hasNext()) {
            name = iteratorListOfAverageEge.next();
            dataset.addValue(mapOfAverageEge.get(name), "ЕГЭ", name);
        }

        //XYSeriesCollection xySeriesCollection = new XYSeriesCollection(xySeries);
        JFreeChart chart = ChartFactory.createBarChart(
                "Распределение по среднему баллу ЕГЭ",
                "Название предмета",
                "Средний балл ЕГЭ",
                dataset,
                PlotOrientation.HORIZONTAL,
                false,
                true,
                false
        );
        CategoryPlot plot = chart.getCategoryPlot();
        plot.setRangeAxisLocation(AxisLocation.BOTTOM_OR_LEFT); // Положение оси
        BarRenderer barRenderer = (BarRenderer) plot.getRenderer();
        barRenderer.setBaseToolTipGenerator(new StandardCategoryToolTipGenerator());
        plot.getRangeAxis().setStandardTickUnits(org.jfree.chart.axis.NumberAxis.createIntegerTickUnits());
        plot.getRangeAxis().setRange(0.0, 100.0);
        return chart;
    }

    private void showGUI() throws Exception {
        // Кнопка "только для зачисленных"
        Checkbox choiceOnlyForEnrolled = new Checkbox("Только для зачисленных");
        Container container = this.getContentPane();
        container.setLayout(new BorderLayout());
        JPanel panel = new JPanel();
        panel.setLayout(new GridLayout(3, 1));
        panel.add(choiceOnlyForEnrolled);

        // Выпадающий список "регионы регистрации"
        ResultSet resultSetOfRegions = this.statement.executeQuery("SELECT id, region_registration_name " +
                "FROM abit_region_registrations " +
                "WHERE is_active = 1 " +
                "ORDER BY region_registration_name");
        List<StringBean> listOfRegions = new ArrayList<>();
        StringBean emptyBean = new StringBean(null, "Все");
        listOfRegions.add(emptyBean);
        while (resultSetOfRegions.next()) {
            listOfRegions.add(new StringBean(resultSetOfRegions.getString("id"),resultSetOfRegions.getString("region_registration_name")));
        }
        JComboBox<StringBean> choiceOfRegion = new JComboBox<>(listOfRegions.toArray(new StringBean[0]));
        panel.add(choiceOfRegion);

        // Выпадающий список "конкурсные группы"
        ResultSet resultSetOfCompGroup = this.statement.executeQuery("SELECT `abit_abiteducompgroup`.id AS id," +
                "CONCAT_WS(', ',CONCAT(`abit_abitspeciality`.specialityNumber,' ',`abit_abitspeciality`.name)," +
                "IF(`abit_eduprofile`.`name`<>'не указан(а)',`abit_eduprofile`.`name`,NULL), abit_edugrade.name, abit_eeduform.name, " +
                "IF(abit_eduduration.isShort = 1, abit_eduduration.name, 'полная'), " +
                "IF(abit_target_languages.target_language_name='нет',NULL,abit_target_languages.target_language_name), " +
                "abit_eedufinancing.name) AS fullName  " +
                "FROM `abit_abiteducompgroup` " +
                "JOIN `abit_abitspeciality` ON `ref_abit_abitspeciality` = `abit_abitspeciality`.id " +
                "JOIN abit_eduprofile ON `ref_abit_eduprofile` = `abit_eduprofile`.id " +
                "JOIN abit_edugrade ON ref_abit_edugrade = abit_edugrade.id " +
                "JOIN abit_eeduform ON ref_abit_eeduform = abit_eeduform.id " +
                "JOIN abit_eduduration ON abit_abiteducompgroup.ref_abit_eduduration = abit_eduduration.id " +
                "JOIN abit_target_languages ON abit_abiteducompgroup.ref_abit_target_languages = abit_target_languages.id " +
                "JOIN abit_eedufinancing ON ref_abit_eedufinancing = abit_eedufinancing.id " +
                "WHERE (`receptionYear` = 2012 AND abit_abiteducompgroup.isActive = 1 AND ref_abit_edugrade <> 163) " +
                "ORDER BY fullName");
        List<StringBean> listOfCompGroup = new ArrayList<>();
        listOfCompGroup.add(emptyBean);
        while (resultSetOfCompGroup.next())
            listOfCompGroup.add(new StringBean(resultSetOfCompGroup.getString("id"), resultSetOfCompGroup.getString("fullName")));
        JComboBox<StringBean> choiceOfCompGroup = new JComboBox<>(listOfCompGroup.toArray(new StringBean[0]));
        panel.add(choiceOfCompGroup);

        container.add(panel, BorderLayout.NORTH);

        this.currentCompGroup = emptyBean;
        this.currentRegion = emptyBean;
        this.onlyEnrolled = false;

        JPanel panelChart = new JPanel();
        panelChart.setLayout(new GridLayout(1, 2));

        final ChartPanel pieChartPanel = new ChartPanel(getPieChart());
        panelChart.add(pieChartPanel);

        final ChartPanel chartPanel = new ChartPanel(getChart());
        panelChart.add(chartPanel);

        container.add(panelChart, BorderLayout.CENTER);
        choiceOnlyForEnrolled.addItemListener(e -> {
            this.onlyEnrolled = e.getStateChange()== ItemEvent.SELECTED;
            try {
                chartPanel.setChart(getChart());
                pieChartPanel.setChart(getPieChart());
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        });
        choiceOfCompGroup.addActionListener((ActionEvent e) -> {
            if (choiceOfCompGroup.getSelectedItem()!=null) this.currentCompGroup = (StringBean) choiceOfCompGroup.getSelectedItem();
            try {
                chartPanel.setChart(getChart());
                pieChartPanel.setChart(getPieChart());
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        });
        choiceOfRegion.addActionListener((ActionEvent e) -> {
            if (choiceOfRegion.getSelectedItem()!=null) this.currentRegion = (StringBean) choiceOfRegion.getSelectedItem();
            try {
                chartPanel.setChart(getChart());
                pieChartPanel.setChart(getPieChart());
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        });
        /*choiceOfRegion.addActionListener((ActionEvent e) -> {
            try {
                ResultSet region = this.compGroup();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            this.currentRegion = (String) choiceOfRegion.getSelectedItem();
            try {
                chartPanel.setChart(getChart());
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        });*/
        this.setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
        this.setSize(1200, 800);
        this.setVisible(true);
    }
/*
    private ResultSet compGroup () throws Exception {
        String query = "SELECT ref_abit_entrance_tests, avg(mark) FROM abit_entrant " +
                "JOIN abit_abiturients ON abit_entrant.ref_abit_abiturients = abit_abiturients.id " +
                "JOIN abit_ege_results ON abit_ege_results.ref_abit_abiturients = abit_abiturients.id WHERE 1";

        // Отбор по конкурсной группе
        if (!this.currentCompGroup.equals("Все")) query += "AND ref_abit_abiteducompgroup = " + this.currentCompGroup;

        // Отбор только по зачисленным
        if (this.onlyEnrolled) query += "AND ref_abit_eabitstate IN (8,9,10,12,13,14)";

        // Отбор по региону
        if (!this.currentRegion.equals("Все")) query += "AND ref_abit_region_registrations = " + this.currentRegion;
        query += "GROUP BY ref_abit_entrance_tests " +
                "HAVING ref_abit_entrance_tests IN " +
                "  (SELECT abit_entrance_tests.id FROM abit_entrance_tests " +
                "  JOIN abit_eduprograms_entrance_tests_links ON abit_eduprograms_entrance_tests_links.ref_abit_entrance_tests = abit_entrance_tests.id " +
                "  JOIN abit_abiteducompgroup ON abit_eduprograms_entrance_tests_links.ref_abit_eduprograms = abit_abiteducompgroup.ref_abit_eduprograms " +
                "  WHERE 1";

        // Отбор по конкурсной группе (для вложенного запроса)
        if (!this.currentCompGroup.equals("Все")) query += "AND abit_abiteducompgroup.id = " + this.currentCompGroup;

        query +="  AND abit_entrance_tests.is_ege = 1";
        ResultSet compGroup = this.statement.executeQuery(query);
        return compGroup;
    }
*/
    private ResultSet averageEge () throws Exception {
        String query = "SELECT ref_abit_entrance_tests AS number, abit_entrance_tests.name, avg(mark) AS average FROM abit_entrant " +
                "JOIN abit_abiturients ON abit_entrant.ref_abit_abiturients = abit_abiturients.id " +
                "JOIN abit_ege_results ON abit_ege_results.ref_abit_abiturients = abit_abiturients.id " +
                "JOIN abit_entrance_tests ON abit_entrance_tests.id = abit_ege_results.ref_abit_entrance_tests " +
                "WHERE abit_entrant.ref_abit_campaigns = 1";

        // Отбор по конкурсной группе
        if (this.currentCompGroup.getId() != null) query += " AND " + this.currentCompGroup.getId() +" IN (ref_abit_abiteducompgroup,ref_abit_abiteducompgroup1,ref_abit_abiteducompgroup2,ref_abit_abiteducompgroup3,ref_abit_abiteducompgroup4)";

        //Отбор только по зачисленным
        if (this.onlyEnrolled) query += " AND ref_abit_eabitstate IN (8,9,10,12,13,14)";

        //Отбор по региону
        if (this.currentRegion.getId() != null) query += " AND ref_abit_region_registrations = " + this.currentRegion.getId();

        query += " GROUP BY ref_abit_entrance_tests " +
                "HAVING ref_abit_entrance_tests IN " +
                "  (SELECT abit_entrance_tests.id FROM abit_entrance_tests " +
                "  JOIN abit_eduprograms_entrance_tests_links ON abit_eduprograms_entrance_tests_links.ref_abit_entrance_tests = abit_entrance_tests.id " +
                "  JOIN abit_abiteducompgroup ON abit_eduprograms_entrance_tests_links.ref_abit_eduprograms = abit_abiteducompgroup.ref_abit_eduprograms " +
                "  WHERE 1";

        //Отбор по конкурсной группе (для вложенного запроса)
        if (this.currentCompGroup.getId() != null) query += " AND abit_abiteducompgroup.id = " + this.currentCompGroup.getId();

        query += "  AND abit_entrance_tests.is_ege = 1 AND abit_entrance_tests.id <> 18)";
        System.out.println(query);
        ResultSet averageEge = this.statement.executeQuery(query);
        return averageEge;
    }
}
